﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace DbHelper
{
    public sealed class DbHelper
    {
        /// <summary>
        /// Private constructor to keep class from being instantiated.
        /// </summary>
        private DbHelper() { }

        /// <summary>
        /// Convert DateTime object to a string reprentation to be used in Xml.
        /// </summary>
        /// <param name="dateTimeToConvert">DateTime object.</param>
        /// <returns>Xml compatible string respresentation of date.</returns>
        public static string DateTimeToXmlDate(DateTime dateTimeToConvert)
        {
            return dateTimeToConvert.ToString("yyyy-MM-ddTHH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
        }

        /// <summary>
        /// Convert Xml date to DateTime object.
        /// </summary>
        /// <param name="xmlDate">String representation of date.</param>
        /// <returns>DateTime object set to string date.</returns>
        public static DateTime XmlDateToDateTime(string xmlDate)
        {
            return System.DateTime.Parse(xmlDate, System.Globalization.CultureInfo.InvariantCulture);
        }

        /// <summary>
        /// Execute a non query with the default connection string
        /// </summary>
        /// <param name="cmd"></param>
        public static void ExecuteSqlNonQuery(System.Data.SqlClient.SqlCommand cmd)
        {
            ExecuteSqlNonQuery(cmd, ConfigurationManager.ConnectionStrings[0].ConnectionString);
        }

        /// <summary>
        /// Execute a non query and pass in a connection string
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="connstr"></param>
        public static void ExecuteSqlNonQuery(System.Data.SqlClient.SqlCommand cmd, string connstr)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(
                connstr
                );
            conn.Open();
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static object ExecuteScalar(System.Data.SqlClient.SqlCommand cmd)
        {
            return ExecuteScalar(cmd, ConfigurationManager.ConnectionStrings[0].ConnectionString);
        }

        public static object ExecuteScalar(System.Data.SqlClient.SqlCommand cmd, string connstr)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(
                connstr
                );
            conn.Open();
            cmd.Connection = conn;
            return cmd.ExecuteScalar();
        }

        public static int ExecuteSqlNonQueryWithReturnInfo(System.Data.SqlClient.SqlCommand cmd)
        {
            return ExecuteSqlNonQueryWithReturnInfo(cmd, ConfigurationManager.ConnectionStrings[0].ConnectionString);
        }

        public static int ExecuteSqlNonQueryWithReturnInfo(System.Data.SqlClient.SqlCommand cmd, string connstr)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(
                connstr
                );
            conn.Open();
            cmd.Connection = conn;
            return cmd.ExecuteNonQuery();
        }

        public static System.Data.SqlClient.SqlDataReader ExecuteSqlReader(System.Data.SqlClient.SqlCommand cmd, string connectionString)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(
                connectionString
                );
            conn.Open();
            cmd.Connection = conn;

            return cmd.ExecuteReader();
        }
    }
}
